package dasom.basic;

import java.io.IOException;
import java.io.PrintWriter;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * Servlet implementation class SungJukV4
 */
@WebServlet(urlPatterns = { "/SungJukV4New", "/SungJukV4NewOk",// 추가
		"/SungJukV4ReadAll", "/SungJukV4ReadOne",// 조회
		"/SungJukV4Update", "/SungJukV4UpdateOk",// 수정
		"/SungJukV4Delete" })
// 삭제
public class SungJukV4 extends HttpServlet {
	private static final long serialVersionUID = 1L;
	private static final String DRV = "oracle.jdbc.OracleDriver";
	private static final String URL = "jdbc:oracle:thin:@//112.216.253.122/xe";
	private static final String USR = "dds889";
	private static final String PWD = "dds889";

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
	 *      response)
	 */
	protected void doGet(HttpServletRequest req, HttpServletResponse res)
			throws ServletException, IOException {
		res.setContentType("text/html; charset=UTF-8");
		// 클라이언트의 모든 요청을 SungJukV4 서블릿이 받아 처리함
		// front controller 패턴 방식
		// 1. 먼저 클라이언트의 요청 uri 을 받아 분석
		String uri = req.getRequestURI();
		System.out.println(req.getRequestURI());

		if (uri.endsWith("SungJukV4New")) {
			SungJukV4Form(req, res);
			// } else if (uri.endsWith("SungJukV4NewOK")) {
			// System.out.println("성적 추가완료");
			// 클라이언트의 요청이 post이므로 doPost에서 처리함
		} else if (uri.endsWith("SungJukV4ReadAll")) {
			selectAllSungJukV4(req, res);// 모든 성적 데이터를 간단히 출력
		} else if (uri.endsWith("SungJukV4ReadOne")) {
			selectOneSungJukV4(req, res);
		} else if (uri.endsWith("SungJukV4Update")) {
			updateSungJukV4(req, res);
		} else if (uri.endsWith("SungJukV4Delete")) {
			deleteSungJukV4(req, res);
		}

	}
	//수정할 학생읭 번호를 입력받아 sungjuk 테이블에서 그 학생의 모든 성적데이터를 조회하고 결과를 html폼 형태로 출력
	private void updateSungJukV4(HttpServletRequest req, HttpServletResponse res) throws IOException {
		String sjno=req.getParameter("sjno");
		String SQL="select * from sungjuk where sjno=?";
		Connection conn=null;
		PreparedStatement ps =null;
		ResultSet rs=null;
		StringBuilder sb=new StringBuilder();
		
		try{
			Class.forName(DRV);
			conn=DriverManager.getConnection(URL,USR,PWD);
			ps=conn.prepareStatement(SQL);
				ps.setString(1, sjno);
				rs=ps.executeQuery();
				if(rs.next()){
					  sb.append("<div>번호 : ").append(rs.getString(1)).append("</div>")
	                  .append("<div>이름 : ").append("<input type='text' name='name' value='").append(rs.getString(2))
	                  .append("'></div>")
	                  .append("<div>국어 : ").append("<input type='text' name='kor' value='").append(rs.getString(3))
	                  .append("'></div>")
	                  .append("<div>영어 : ").append("<input type='text' name='eng' value='").append(rs.getString(4)).append("'></div>")
	                  .append("<div>수학 : ").append("<input type='text' name='mat' value='").append(rs.getString(5)).append("'></div>")
	                  .append("<div>총점 : ").append(rs.getString(6)).append("</div>")
	                  .append("<div>평균 : ").append(rs.getString(7)).append("</div>")
	                  .append("<div>학점 : ").append(rs.getString(8)).append("</div>")
	                  .append("<div>등록일 : ").append(rs.getString(9)).append("</div>")
	                  .append("<input type='hidden' name='sjno' value='")
	                  .append(sjno).append("'>");
	                  
				}
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			if (rs != null)
				try {
					rs.close();
				} catch (Exception e) {
				}
			if (ps != null)
				try {
					ps.close();
				} catch (Exception e) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (Exception e) {
				}
		}
		
		res.setContentType("text/html;charset=UTF-8");
		PrintWriter out = res.getWriter();
		
		out.println("<html>");
		out.print("<head><title>성적 수정하기");
		out.print("</title></head>");
		out.print("<body><h1>성적 수정하기</h1>");
		out.print("<form method='post' action='./SungJukV4UpdateOk'>");
		out.print(sb.toString());
		out.print("<button type='submit' >수정완료</button>");
		out.print("<button type='button' id='cancelBtn'>수정취소</button>");
		out.print("</div></form>");
		
		out.println("<script type='text/javascript'>");
		out.println("var cancelBtn=document.getElementById('cancelBtn');");
		out.println("cancelBtn.onclick=function(){go2back();};");
		out.println("function go2back(){");
		out.println("location.href='./SungJukV4ReadAll';");
		out.println("}");
		out.println("</script>");
		out.println("</body>");
		out.println("</html>");
	

	}
	//삭제할 학생의 번호를 입력받아 sungjuk테이블에서 삭제
	private void deleteSungJukV4(HttpServletRequest req, HttpServletResponse res) throws IOException {
		String sjno = req.getParameter("sjno");
		String SQL = "delete from sungjuk where sjno=?";
		Connection conn = null;
		PreparedStatement ps = null;

		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(SQL);
				ps.setString(1, sjno);
				int cnt=ps.executeUpdate();
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {

			if (ps != null)
				try {
					ps.close();
				} catch (Exception e) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (Exception e) {

				}
		}
		//삭제후 전체 성적 출력(SungJukV4ReadAll)페이지로 이동
		res.sendRedirect("./SungJukV4ReadAll");
		
	}	

	// sungjuk테이블에 저장된 모든 데이터 중 특정 학생에 대해 상세하게(번호,이름,국어,영어,수학,총점,평균,학점,등록일)출력
	private void selectOneSungJukV4(HttpServletRequest req,
			HttpServletResponse res) throws IOException {
		String SQL = "select * from sungjuk where sjno=?";
		StringBuilder sb = null;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		// 클라이언트 요청 중 질의문자열에서 sjno값을 받아 sjno변수에 저장
		String sjno = req.getParameter("sjno");

		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(SQL);
			ps.setString(1, sjno);
			rs = ps.executeQuery();

			if (rs.next()) {
				sb = new StringBuilder();
				sb.append("<div>번호:").append(rs.getString(1))
						.append("<br/>이름:").append(rs.getString(2))
						.append("<br/>국어:").append(rs.getString(3))
						.append("<br/>영어:").append(rs.getString(4))
						.append("<br/>수학:").append(rs.getString(5))
						.append("<br/>총점:").append(rs.getString(6))
						.append("<br/>평균:").append(rs.getString(7))
						.append("<br/>학점:").append(rs.getString(8))
						.append("<br/>등록일:").append(rs.getString(9))
						.append("<div>");
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (Exception e) {
				}
			if (ps != null)
				try {
					ps.close();
				} catch (Exception e) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (Exception e) {
				}
			res.setContentType("text/html;charset=UTF-8");
			PrintWriter out = res.getWriter();

			out.print("<!DOCTYPE html>");
			out.print("<html>");
			out.print("<head><title>성적 상세보기");
			out.print("</title></head>");
			out.print("<body><h1>성적 상세보기</h1>");

			out.println(sb.toString());
			out.println("<div><a href='./SungJukV4ReadAll'>목록으로</a>");
		      out.println("<a href='./SungJukV4Update?sjno=" + sjno + "'>수정하기</a>");
		      out.println("<a href='javascript:isDelete(" + sjno
		            + ");'>삭제하기</a></div>");

		      out.print("<script type='text/javascript'>");
		      out.print("function isDelete(sjno){");
		      out.print("alert(sjno);");
		      out.print("if(confirm('정말로,사실로,참말로 삭제?'))");
		      out.print("location.href='./SungJukV4Delete?sjno=" + sjno + "';");
		      out.print("");
		      out.print("}");
		      out.print("</script>");
		      out.print("</body>");
		      out.print("</html>");
			

		}

	}

	// 클라이언트의 요청이 /SungJukV4Form인 경우 처리할 내용
	// 이름,국어,영어,수학을 입력할 수 있도록 html폼을 작성
	// 입력완료 버튼을 클릭하면 SungJukV4NewOk로 submit함
	private void SungJukV4Form(HttpServletRequest req, HttpServletResponse res)
			throws ServletException, IOException {

		res.setContentType("text/html;charset=UTF-8");
		PrintWriter out = res.getWriter();

		out.print("<!DOCTYPE html>");
		out.print("<html>");
		out.print("<head><title>성적 입력하기");
		out.print("</title></head>");
		out.print("<body><h1>성적 입력하기</h1>");

		out.print("<form name='sj' method='post'action='SungJukV4NewOk'>");
		out.print("<div>이름<input type='text'name='name'></div>");
		out.print("<div>국어<input type='text'name='kor'></div>");
		out.print("<div>영어<input type='text'name='eng'></div>");
		out.print("<div>수학<input type='text'name='mat'></div>");
		out.print("<div><p><a href='./SungJukV4ReadAll'>목록(SungJukV4ReadAll)</a></p></div>");

		out.print("<div><button type='submit'>입력완료</button></div>");
		out.print("<div><button type='submit'>다시입력</button></div>");
		out.print("</form>");
		out.print("</body>");
		out.print("</html>");

	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
	 *      response)
	 */
	protected void doPost(HttpServletRequest req, HttpServletResponse res)
			throws ServletException, IOException {
		String uri = req.getRequestURI();
		System.out.println(uri);

		// 클라이언트의 post요청 uri가 SungJukV4NewOk로 끝나면
		// insertSungJukV4 메서드 호출
		if (uri.endsWith("SungJukV4NewOk")) {
			insertSungJukV4(req, res);// 성적 데이터db추가

		} else if (uri.endsWith("SungJukV4ReadAll")) {
			selectAllSungJukV4(req, res);// 전체 성적 데이터를 간단히 출력
		} else if (uri.endsWith("SungJukV4UpdateOk")) {
			updateOkSungJukV4(req, res);
		}

	}
	//학생성적을 수정하면 수정값뿐만아니라 전체 값을 다시 입력받아 총점, 평균, 학점을 계산한 후 sungjuk 테이블에 저장
	private void updateOkSungJukV4(HttpServletRequest req,
			HttpServletResponse res) throws IOException {
		String SQL ="update sungjuk set name=?, kor=?, eng=?, mat=? where sjno=?";
		
		Connection conn=null;
		PreparedStatement ps=null;
		
		req.setCharacterEncoding("UTF-8");//오청 객체에 대한 한글처리
		String sjno=req.getParameter("sjno");
		String name=req.getParameter("name");
		int kor=Integer.parseInt(req.getParameter("kor"));
		int eng=Integer.parseInt(req.getParameter("eng"));
		int mat=Integer.parseInt(req.getParameter("mat"));
		
		try{
			Class.forName(DRV);
			conn=DriverManager.getConnection(URL,USR,PWD);
			ps=conn.prepareStatement(SQL);
				ps.setString(1, name);
				ps.setInt(2, kor);
				ps.setInt(3, eng);
				ps.setInt(4, mat);
				ps.setString(5, sjno);
				int cnt = ps.executeUpdate();
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			if (ps != null)
				try {
					ps.close();
				} catch (Exception e) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (Exception e) {
				}
		}
		//수정하고 난후 SungJukV4ReadAll로 페이지 이동
		res.sendRedirect("./SungJukV4ReadAll");
		
	}

	// sungjuk테이블에 저장된 모든 데이터를 간단하게(번호,이름,국어,영어,수학)출력
	private void selectAllSungJukV4(HttpServletRequest req,
			HttpServletResponse res) throws ServletException, IOException {
		String SQL = "select sjno,name,kor,eng,mat from sungjuk order by sjno desc";
		StringBuilder sb = null;
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(SQL);
			rs = ps.executeQuery();

			sb = new StringBuilder();
			while (rs.next()) {
				sb.append("<div>").append(rs.getString(1)).append("")
						.append("<a href='./SungJukV4ReadOne?sjno=")
						.append(rs.getString(1)).append("'>")

						.append(rs.getString(2)).append("</a>")
						.append(rs.getString(3)).append("")
						.append(rs.getString(4)).append("")
						.append(rs.getString(5)).append("<br/>");
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (Exception e) {
				}
			if (ps != null)
				try {
					ps.close();
				} catch (Exception e) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (Exception e) {
				}
		}
		// 결과를 html형태로 출력
		res.setContentType("text/html;charset=UTF-8");
		PrintWriter out = res.getWriter();
		out.print("<!DOCTYPE html>");
		out.print("<html>");
		out.print("<head><title>성적 전체조회");
		out.print("</title></head>");
		out.print("<body><h1>성적 전체조회</h1>");
		out.println(sb.toString());
		
		out.print("</body>");
		out.print("</html>");

	}

	// 클라이언트가 입력한 성적데이터를 요청객체로 받은 다음 JDBC를 이용해서 오라클 DB에 추가
	private void insertSungJukV4(HttpServletRequest req, HttpServletResponse res)
			throws IOException {
		String SQL = "insert into sungjuk(sjno,name,kor,eng,mat) values(sjno.nextval,?,?,?,?)";
		Connection conn = null;
		PreparedStatement ps = null;

		// 요청객체로 부터 입력데이터 가져오기
		// 요청객체 한글처리
		req.setCharacterEncoding("UTF-8");
		String name = req.getParameter("name");
		int kor = Integer.parseInt(req.getParameter("kor"));
		int eng = Integer.parseInt(req.getParameter("eng"));
		int mat = Integer.parseInt(req.getParameter("mat"));

		// 총점,평균,학점 계산

		// 처리된 결과를 sungjuk 테이블에 저장
		try {
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL, USR, PWD);
			ps = conn.prepareStatement(SQL);
			ps.setString(1, name);
			ps.setInt(2, kor);
			ps.setInt(3, eng);
			ps.setInt(4, mat);
			int cnt = ps.executeUpdate();

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (ps != null)
				try {
					ps.close();
				} catch (Exception e) {
				}
			if (conn != null)
				try {
					conn.close();
				} catch (Exception e) {
				}
		}
		// 처리결과를 html형태로 작성해서 응답으로 출력
		res.setContentType("text/html;charset=UTF-8");
		PrintWriter out = res.getWriter();
		out.print("<!DOCTYPE html>");
		out.print("<html>");
		out.print("<head><title>성적 입력결과");
		out.print("</title></head>");
		out.print("<body><h1>성적 입력결과</h1>");
		out.print("<div>이름:" + name + "</div>");
		out.print("<div>국어:" + kor + "</div>");
		out.print("<div>영어:" + eng + "</div>");
		out.print("<div>수학:" + mat + "</div>");
		out.print("<div><p>목록(SungJukV4ReadAll)으로 이동</p></div>");
		out.print("</body>");
		out.print("</html>");
	}

}
